﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Matrix.Common.DBUtility;
using Matrix.MaKaYunShang.Model;

namespace Matrix.MaKaYunShang.DAL
{
    public partial class CardRuleDetailDAL : BaseDAL<CardRuleDetailDAL>
    {
        #region 默认方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(CardRuleDetail model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into CardRuleDetail(");
            strSql.Append("RuleBaseId,CloudCardId,CardSendQuantity,CardTotalQuantity,AddTime,IsDelete");
            strSql.Append(") values (");
            strSql.Append("@RuleBaseId,@CloudCardId,@CardSendQuantity,@CardTotalQuantity,@AddTime,@IsDelete");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@RuleBaseId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CloudCardId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CardSendQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@CardTotalQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@AddTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.RuleBaseId;
            parameters[1].Value = model.CloudCardId;
            parameters[2].Value = model.CardSendQuantity;
            parameters[3].Value = model.CardTotalQuantity;
            parameters[4].Value = model.AddTime;
            parameters[5].Value = model.IsDelete; var obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(CardRuleDetail model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CardRuleDetail set ");

            strSql.Append(" RuleBaseId = @RuleBaseId , ");
            strSql.Append(" CloudCardId = @CloudCardId , ");
            strSql.Append(" CardSendQuantity = @CardSendQuantity , ");
            strSql.Append(" CardTotalQuantity = @CardTotalQuantity , ");
            strSql.Append(" AddTime = @AddTime , ");
            strSql.Append(" IsDelete = @IsDelete  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@RuleBaseId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CloudCardId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CardSendQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@CardTotalQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@AddTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.RuleBaseId;
            parameters[2].Value = model.CloudCardId;
            parameters[3].Value = model.CardSendQuantity;
            parameters[4].Value = model.CardTotalQuantity;
            parameters[5].Value = model.AddTime;
            parameters[6].Value = model.IsDelete; return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CardRuleDetail set IsDelete=1 ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CardRuleDetail set IsDelete=1 ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModel(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, RuleBaseId, CloudCardId, CardSendQuantity, CardTotalQuantity, AddTime, IsDelete  ");
            strSql.Append("  from CardRuleDetail ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM CardRuleDetail where IsDelete=0 ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion

        #region 扩展方法
        /// <summary>
        /// 查询 可发卡卷规则
        /// </summary>
        /// <remarks>create lk 2016-06-13</remarks>

        /// <returns></returns>
        public DataSet GetModelByRuleBaseId(int ruleBaseId, int cloudCardId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id, RuleBaseId, CloudCardId, CardSendQuantity, CardTotalQuantity, AddTime, IsDelete  ");
            strSql.Append("  from CardRuleDetail ");
            strSql.Append(" where ruleBaseId=@ruleBaseId and cloudCardId=@cloudCardId  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@ruleBaseId", SqlDbType.Int,4),
                    	new SqlParameter("@cloudCardId", SqlDbType.Int,4)
			};
            parameters[0].Value = ruleBaseId;
            parameters[1].Value = cloudCardId;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 查询 可发卡卷列表
        /// </summary>
        /// <remarks>create lk 2016-06-13</remarks>
        public DataSet GetModelByRuleBaseId(int ruleBaseId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT ");
            strSql.Append("  cc.DisabledSeparatedIndustryIds,cc.ShowOnHomepage,s.Id as ShopId,s.ParentShopId,s.IsHeadquarters,cc.StartTime,cc.EndTime, crd.*,cdc.CategoryCode,cc.TotalQuantity,cc.LoGo,cc.Title,cc.CardName,cc.CardValue ");
            strSql.Append("  FROM CardRuleDetail crd  ");
            strSql.Append("  left join CloudCards cc on crd.CloudCardId=cc.id ");
            strSql.Append("  left join CardCategory cdc on cdc.Id=cc.CardCategoryId ");
            strSql.Append("  left join shops s on s.id=cc.shopid  ");
            strSql.Append("  where crd.RuleBaseId=@RuleBaseId and crd.IsDelete=0 and cc.IsDelete=0 and crd.IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@RuleBaseId", SqlDbType.Int,4)
			};
            parameters[0].Value = ruleBaseId;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 按条件查询 可发卡卷列表
        /// </summary>
        /// <remarks>create lk 2016-06-13</remarks>
        public DataSet GetModelListByWhere(string strWhere, string longitude, string latitude, float radius)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT cc.Instruction,s.Name as ShopName,cc.ShopId,(ss.TotalCanSendCard-ss.AlreadySendCard) as canSendCard, cc.StartTime,cc.EndTime,crd.*,cc.CategoryCode,");
            strSql.Append(" cc.TotalQuantity,cc.LoGo,cc.Title,cc.CardName ,s.Latitude,s.Longitude,cc.CardValue  ");
            strSql.Append(" FROM  [CardRuleDetail]  crd  ");
            strSql.Append(" left join CloudCards cc on crd.CloudCardId=cc.id ");
            strSql.Append(" left join shops s on s.id=cc.ShopId  left join ShopSubsidiaryInfo ss on ss.ShopId=s.id");
            strSql.Append(" where  crd.isdelete=0 and s.isdelete=0 and cc.isdelete=0  and dbo.fnGetDistance( @Latitude, @Longitude, Latitude, Longitude)<@Radius");
            strSql.Append(" and crd.RuleBaseId in (select max(id) from  CardRuleBase where shopid=cc.shopId and isdelete=0  and StartTime<=CONVERT(varchar(100), getdate(), 23) and EndTime>=CONVERT(varchar(100), getdate(), 23) )  and s.CanSendCard=1 ");
            SqlParameter[] parameters = {
					new SqlParameter("@Longitude", SqlDbType.VarChar)
                    ,new SqlParameter("@Latitude", SqlDbType.VarChar)
                    ,new SqlParameter("@Radius", SqlDbType.Int,4)
			};
            parameters[0].Value = longitude;
            parameters[1].Value = latitude;
            parameters[2].Value = radius;
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }


            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 跟进RuleBaseId删除多条数据
        /// </summary>
        /// <remarks>2016-07-01 万乐园 创建</remarks>
        public bool DeleteByRuleBaseId(int ruleBaseId)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CardRuleDetail set IsDelete=1 ");
            strSql.Append(" where RuleBaseId=@RuleBaseId");
            SqlParameter[] parameters = {
					new SqlParameter("@RuleBaseId", SqlDbType.Int,4)
			};
            parameters[0].Value = ruleBaseId;
            var rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            return rows > 0;
        }
        #endregion
    }
}
